#!/usr/bin/python3

import os
import pymysql
import pyecharts.options as opts
from pyecharts.charts import Line, Grid

##connect info
mtsql_host = os.getenv('MTSQL_METDATA_HOST')
mtsql_port_str = os.getenv('MTSQL_METDATA_PORT')
mtsql_port = int(mtsql_port_str)
mtsql_user = os.getenv('MTSQL_METDATA_USER')
mtsql_password = os.getenv('MTSQL_METDATA_PASSWORD')
mtsql_database = os.getenv('MTSQL_METDATA_DATABASE')
mtsql_charset = os.getenv('MTSQL_METDATA_CHARSET')

##test info
mtsql_pt_result_graph_path=os.getenv('MTSQL_PT_RESULT_GRAPH_PATH')
mtsql_pt_feature = os.getenv('MTSQL_PT_FEATURE')
mtsql_pt_with_feature = os.getenv('MTSQL_PT_WITH_FEATURE')
mtsql_pt_test_scenarios = os.getenv('MTSQL_PT_TEST_SCENARIOS')
mtsql_pt_sub_test_scenarios = os.getenv('MTSQL_PT_SUB_TEST_SCENARIOS')
mtsql_pt_db_type = os.getenv('MTSQL_PT_DB_TYPE')
mtsql_pt_test_case = os.getenv('MTSQL_PT_TEST_CASE')
mtsql_pt_test_batch_no = os.getenv('MTSQL_PT_TEST_BATCH_NO')
mtsql_pt_os_name = os.getenv('MTSQL_PT_OS_NAME')
mtsql_pt_machine_hardware_name = os.getenv('MTSQL_PT_MACHINE_HARDWARE_NAME')
mtsql_pt_deploy_type = os.getenv('MTSQL_PT_DEPLOY_TYPE')
mtsql_pt_deploy_type = mtsql_pt_deploy_type.lower()

#loop
mtsql_pt_test_cases= ['oltp_read_write', 'oltp_read_only', 'oltp_update_non_index', 'oltp_point_select', 'oltp_write_only']
#mtsql_pt_test_cases= ['oltp_read_write']

#key name
mtsql_latency_min_name='latency_max'
mtsql_latency_avg_name='latency_max'
mtsql_latency_max_name='latency_max'
mtsql_percentile_name='percentile'


db= pymysql.connect(host=mtsql_host, port=mtsql_port, user=mtsql_user, password=mtsql_password, database=mtsql_database, charset=mtsql_charset)

cursor = db.cursor()

sql= "SELECT threads,value " \
     "FROM pt_feature_result_info " \
     "where feature = %s " \
     "and with_feature= %s " \
     "and test_scenarios= %s " \
     "and sub_test_scenarios= %s " \
     "and db_type= %s " \
     "and test_case= %s " \
     "and test_batch_no= %s " \
     "and os_name= %s " \
     "and machine_hardware_name= %s " \
     "and deploy_type= %s " \
     "and name= %s " \
     "order by CAST(threads as UNSIGNED)"

for mtsql_pt_test_case in mtsql_pt_test_cases:

    try:
        print("result latency base graph for test case=", mtsql_pt_test_case)
        #common
        graph_file_name = mtsql_pt_result_graph_path \
                        + "/pt_result_latency_base_" \
                        + mtsql_pt_deploy_type \
                        + "_" \
                        + mtsql_pt_feature \
                        + "_" \
                        + mtsql_pt_with_feature \
                        + "_" \
                        + mtsql_pt_test_scenarios \
                        + mtsql_pt_test_batch_no \
                        + "_" \
                        + mtsql_pt_db_type \
                        + "_" \
                        + mtsql_pt_test_case \
                        + ".html"

        ##query percentile info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_percentile_name)
        mtsql_version_list = []
        mtsql_percentile_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_percentile_list.append(value_float)
        # graph
        print("result graph for percentile!")
        percentile_line_max = max(mtsql_percentile_list)
        percentile_line_max = int(percentile_line_max / 10) * 10
        percentile_line_max = percentile_line_max + 100
        percentile_line_min = 0
        percentile_line = (
            Line()
                .add_xaxis(xaxis_data=mtsql_version_list)
                .add_yaxis(
                    series_name="结果值",
                    y_axis=mtsql_percentile_list,
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                            opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    ),
                    is_smooth=True,
                )
                .set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                                          name_gap=5,
                                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                                 yaxis_opts=opts.AxisOpts(
                                     name="percentile999(ms)",
                                     max_=percentile_line_max,
                                     min_=percentile_line_min,
                                     max_interval=50000,
                                     min_interval=10000),
                                 )
        )

        ##query latency_max info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_latency_max_name)
        mtsql_version_list = []
        mtsql_latency_max_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_latency_max_list.append(value_float)
        # graph
        print("result graph for latency_max!")
        latency_max_line_max = max(mtsql_latency_max_list)
        latency_max_line_max = int(latency_max_line_max / 10) * 10
        latency_max_line_max = latency_max_line_max + 100
        latency_max_line_min = 0
        latency_max_line = (
            Line()
                .add_xaxis(xaxis_data=mtsql_version_list)
                .add_yaxis(
                    series_name="结果值",
                    y_axis=mtsql_latency_max_list,
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                            opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    ),
                    is_smooth=True,
                )
                .set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                                          name_gap=5,
                                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                                 yaxis_opts=opts.AxisOpts(
                                     name="latency_max(ms)",
                                     max_=latency_max_line_max,
                                     min_=latency_max_line_min,
                                     max_interval=50000,
                                     min_interval=10000),
                                 )
        )

        ##query latency_avg info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_latency_avg_name)
        mtsql_version_list = []
        mtsql_latency_avg_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_latency_avg_list.append(value_float)
        # graph
        print("result graph for latency_avg!")
        latency_avg_line_max = max(mtsql_latency_avg_list)
        latency_avg_line_max = int(latency_avg_line_max / 10) * 10
        latency_avg_line_max = latency_avg_line_max + 100
        latency_avg_line_min = 0
        latency_avg_line = (
            Line()
                .add_xaxis(xaxis_data=mtsql_version_list)
                .add_yaxis(
                series_name="结果值",
                y_axis=mtsql_latency_avg_list,
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                            opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    ),
                    is_smooth=True,
                )
                .set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                                          name_gap=5,
                                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                                 yaxis_opts=opts.AxisOpts(
                                     name="latency_avg(ms)",
                                     max_=latency_avg_line_max,
                                     min_=latency_avg_line_min,
                                     max_interval=50000,
                                     min_interval=10000),
                                 )
        )

        ##query latency_min info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_latency_min_name)
        mtsql_version_list = []
        mtsql_latency_min_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_latency_min_list.append(value_float)
        # graph
        print("result graph for latency_min!")
        latency_min_line_max = max(mtsql_latency_min_list)
        latency_min_line_max = int(latency_min_line_max / 10) * 10
        latency_min_line_max = latency_min_line_max + 100
        latency_min_line_min = 0
        latency_min_line = (
            Line()
                .add_xaxis(xaxis_data=mtsql_version_list)
                .add_yaxis(
                    series_name="结果值",
                    y_axis=mtsql_latency_min_list,
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                            opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    ),
                    is_smooth=True,
                )
                .set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                                          name_gap=5,
                                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                                 yaxis_opts=opts.AxisOpts(
                                     name="latency_min(ms)",
                                     max_=latency_min_line_max,
                                     min_=latency_min_line_min,
                                     max_interval=50000,
                                     min_interval=10000),
                                 )
        )

        grid = (
            Grid(init_opts=opts.InitOpts(width="1500px", height="800px"))
                .add(percentile_line,
                     grid_opts=opts.GridOpts(pos_top="10%", pos_bottom="55%", pos_left="5%", pos_right="55%"))
                .add(latency_max_line,
                     grid_opts=opts.GridOpts(pos_top="10%", pos_bottom="55%", pos_left="55%", pos_right="5%"))
                .add(latency_avg_line,
                     grid_opts=opts.GridOpts(pos_top="55%", pos_bottom="10%", pos_left="5%", pos_right="55%"))
                .add(latency_min_line,
                     grid_opts=opts.GridOpts(pos_top="55%", pos_bottom="10%", pos_left="55%", pos_right="5%"))
                .render(graph_file_name)
        )
    except Exception as e:
        print("result latency base graph catch exception!")
        print(e)

db.close()

